package ProjectServlet;
import java.awt.geom.FlatteningPathIterator;
import java.lang.annotation.ElementType;
import java.sql.*;
import java.util.Scanner;
public class ConnectMySql {

    //com.mysql.jdbc.Driver
    private static final String DRIVER_NAME = "com.mysql.cj.jdbc.Driver";
    //数据库连接地址
    private static final String URL = "jdbc:mysql://localhost:3306/finalwork";
    private String USER_NAME ; //用户名
    private String PASSWORD ; //密码
    private Connection connection = null;
    private Statement statement = null;
    private PreparedStatement ps= null;
    private boolean isNext;

    public ConnectMySql(String username, String password){
        USER_NAME = "root";
        PASSWORD = "lishunlin";
    }

    public boolean getConnection() {

        try {
            System.out.println(USER_NAME);
            System.out.println(PASSWORD);
            Class.forName(DRIVER_NAME);
            //获取数据库连接

            connection = DriverManager.getConnection(URL, USER_NAME, PASSWORD);
            System.out.println("数据库访问成功!!");
            return true;
        }catch (Exception e){
            System.out.println("出现错误!!");
            e.printStackTrace();
            return false;
        }
    }

    public String getLevel(String name,String password){
        String sql ="select* from Users where userName=? and passwrod=?";
        try {
            ps =connection.prepareStatement(sql);
            ps.setString(1,name);
            ps.setString(2,password);
            ResultSet rs = ps.executeQuery();
            String l;
            while (rs.next()){
                return  rs.getString("userLevel");
            }
            return "null";
        }catch (Exception e){
            return "null";
        }
    }
    public boolean next(){
        return isNext;
    }
    public ResultSet selectCommunity(String s){
        String sql;
        sql = "call b('" +s+ "')";
        try {
            ps = connection.prepareStatement(sql);
            ResultSet rs;
            rs=ps.executeQuery();
            return rs;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }
    }
    public ResultSet selectHouseType(String s){
        String sql;
        sql = "call houseType('"+s+"')";
        try {

            ps = connection.prepareStatement(sql);
            ResultSet rs=ps.executeQuery();
            return  rs;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }
    }
    public ResultSet selectPrice(double min,double max){
        String sql = "call price("+min+","+max+")";
        try {
            ps = connection.prepareStatement(sql);
            ResultSet rs=ps.executeQuery();
            return rs;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }
    }
    public ResultSet selectArea(double min,double max){
        String sql= "call price("+min+","+max+")";

        try {
            ps = connection.prepareStatement(sql);
            ResultSet rs=ps.executeQuery();
            return rs;

        }catch (Exception e){
            e.printStackTrace();
            return null;
        }
    }

    public ResultSet findSurroundingTraffic( int id){
        String sql="select community.longitude,community.latitude,community.id from house,community " +
                "where community.name=house.community and house.id=?";
        String sql2 = "select  any_value(name),any_value(`type`) ,any_value(longitude),any_value(latitude),ST_Distance_Sphere(point (longitude , latitude),point(?,?)) AS distance " +
                "from " +"交通设施"+
                " group by distance asc limit 100";
        return findHouseSurrounding(sql,sql2,id);
    }
    public ResultSet findSurroundingPublic( int id){
        String sql="select community.longitude,community.latitude,community.id from house,community " +
                "where community.name=house.community and house.id=?";
        String sql2 = "select  any_value(name),any_value(`type`) ,any_value(longitude),any_value(latitude),ST_Distance_Sphere(point (longitude , latitude),point(?,?)) AS distance " +
                "from " +"公共设施"+
                " group by distance asc limit 100";
        return findHouseSurrounding(sql,sql2,id);
    }
    public ResultSet findSurroundingCater(int id){
        String sql="select community.longitude,community.latitude,community.id from house,community " +
                "where community.name=house.community and house.id=?";
        String sql2 = "select  any_value(name),any_value(`type`) ,any_value(longitude),any_value(latitude),ST_Distance_Sphere(point (longitude , latitude),point(?,?)) AS distance " +
                "from " +"餐饮服务"+
                " group by distance asc limit 100";
        return findHouseSurrounding(sql,sql2,id);
    }
    public ResultSet findSurroundingShop(int id){
        String sql="select community.longitude,community.latitude,community.id from house,community " +
                "where community.name=house.community and house.id=?";
        String sql2 = "select  any_value(name),any_value(`type`) ,any_value(longitude),any_value(latitude),ST_Distance_Sphere(point (longitude , latitude),point(?,?)) AS distance " +
                "from " +"购物服务"+
                " group by distance asc limit 100";
        return findHouseSurrounding(sql,sql2,id);
    }
    public ResultSet findSurroundingLifeServer(int id){
        String sql="select community.longitude,community.latitude,community.id from house,community " +
                "where community.name=house.community and house.id=?";
        String sql2 = "select  any_value(name),any_value(`type`) ,any_value(longitude),any_value(latitude),ST_Distance_Sphere(point (longitude , latitude),point(?,?)) AS distance " +
                "from " +"生活服务"+
                " group by distance asc limit 100";
        return findHouseSurrounding(sql,sql2,id);
    }
    public ResultSet findSurroundingScenery(int id){
        String sql="select community.longitude,community.latitude,community.id from house,community " +
                "where community.name=house.community and house.id=?";
        String sql2 = "select  any_value(name),any_value(`type`) ,any_value(longitude),any_value(latitude),ST_Distance_Sphere(point (longitude , latitude),point(?,?)) AS distance " +
                "from " +"风景名胜"+
                " group by distance asc limit 100";
        return findHouseSurrounding(sql,sql2,id);
    }
    public ResultSet findSurroundSchool(int id){
        String sql="select community.longitude,community.latitude,community.id from house,community " +
                "where community.name=house.community and house.id=?";
        String sql2 = "select  any_value(name),any_value(`type`) ,any_value(longitude),any_value(latitude),ST_Distance_Sphere(point (longitude , latitude),point(?,?)) AS distance " +
                "from school group by distance asc limit 100";
        return findHouseSurrounding(sql,sql2,id);
    }
    public ResultSet findSurroundHospital(int id){
        String sql="select community.longitude,community.latitude,community.id from house,community " +
                "where community.name=house.community and house.id=?";
        String sql2 = "select  any_value(name),any_value(longitude),any_value(latitude),ST_Distance_Sphere(point (longitude , latitude),point(?,?)) AS distance " +
                "from hospital group by distance asc limit 100";
        return findHouseSurrounding(sql,sql2,id);
    }


    public ResultSet  multipleSelect(String adm,String houseType,String price,String area){

        String conditions="";

        if(!adm.isEmpty()) {
            conditions+= "and adm_region='"+adm+"'";
        }
        else conditions+=" ";

        if (!houseType.isEmpty()) {
            conditions+=" and houseType= '"+houseType+"'";
        }
        else conditions+=" ";

        if(!price.isEmpty()) {
            String []interval = price.split(",");
            conditions+=" and totalPrice between "+interval[0]+" and  "+interval[1];
        }
        else conditions+=" ";

        if(!area.isEmpty()){
            String []interval2 = area.split(",");
            conditions+=" and allArea between "+interval2[0]+" and  "+interval2[1];
        }
        else conditions+=" ";
        String sql = "select house.id,houseType,totalPrice," +
                " averagePrice,allArea,insideArea,forward,community,adm_region,Hfloor," +
                "totalFloor,years,types from house, community where house.community=community.name "+conditions;

        try {
            ps = connection.prepareStatement(sql);
            return ps.executeQuery();
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }

    }



    public ResultSet spaceSelect(String type,int i){
        String sql = switch (i) {
            case 1 -> "select * from hospital where name like '" + type + "%'";
            case 2 -> "select * from school where name like '" + type + "%'";
            case 3 -> "select * from surrounding2 where type like '" + type + "%'";
            default -> " ";
        };
        try {
            ps = connection.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            return rs;

        }catch (Exception e){
            e.printStackTrace();
            return null;
        }
    }

    public ResultSet findSurroundCommunity(int id){

        try {

            String sql2 = "select * from surrounding2 where id=?";
            ps=connection.prepareStatement(sql2);
            ps.setInt(1,id);
            ResultSet rs2 = ps.executeQuery();
            double longitude=0;
            double latitude=0;
            while (rs2.next()){
                longitude = rs2.getDouble("longitude");
                latitude = rs2.getDouble("latitude");
            }
            String sql3="select any_value(id), any_value(name),any_value(adm_region) ,ST_Distance_Sphere(point (longitude , latitude),point(?,?)) AS distance " +
                    "from community group by distance asc limit 20";
            ps=connection.prepareStatement(sql3);
            ps.setDouble(1,longitude);
            ps.setDouble(2,latitude);
            ResultSet rs = ps.executeQuery();
            return rs;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }
    }


    public ResultSet showCommunityHouse (int id){
            String sql = "select *from house ,community where house.community=community.name and community.id=?";
            try {
                ps = connection.prepareStatement(sql);
                ps.setInt(1,id);
                return ps.executeQuery();

            }catch (Exception e){
                e.printStackTrace();
                return null;
            }


    }

    private ResultSet findHouseSurrounding(String sql,String sql2,int id){
        double longitude=0;
        double latitude=0;
        try {
            ps=connection.prepareStatement(sql);
            ps.setInt(1,id);
            ResultSet rs = ps.executeQuery();
            while (rs.next()){
                longitude = rs.getDouble("longitude");
                latitude = rs.getDouble("latitude");

                break;
            }
            ps = connection.prepareStatement(sql2);
            ps.setDouble(1,longitude);
            ps.setDouble(2,latitude);
            return  ps.executeQuery();

        }catch (Exception e){
            e.printStackTrace();
            return null;
        }
    }

    public ResultSet findCommunityById(String id){
        String sql = "select* from community where id="+id;
        try {
            ps=connection.prepareStatement(sql);
            return  ps.executeQuery();
        }catch (Exception e){
            e.printStackTrace();
            return  null;
        }
    }

    public ResultSet findHouseById(String id){
        String sql = "select* from house where id="+id;
        try {
            ps=connection.prepareStatement(sql);
            return  ps.executeQuery();
        }catch (Exception e){
            e.printStackTrace();
            return  null;
        }
    }
    public boolean addCommunity(String[] value){
        String sql = "insert into community(name,longitude,latitude,adm_region,sale_state,type,price,volume_fraction,greening_rate) " +
                "values(?,?,?,?,?,?,?,?,?)";
        try {
            ps = connection.prepareStatement(sql);
            ps.setString(1,value[0]);

            if(value[1].isEmpty()) ps.setDouble(2,0);
            else ps.setDouble(2,Double.parseDouble(value[1]));

            if(value[2].isEmpty()) ps.setDouble(3,0);
            else ps.setDouble(3,Double.parseDouble(value[2]));

            ps.setString(4,value[3]);
            ps.setString(5,value[4]);
            ps.setString(6,value[5]);
            ps.setString(7,value[6]);
            ps.setString(8,value[7]);
            ps.setString(9,value[8]);
            ps.executeUpdate();
            System.out.println("增加成功!");
            return true;

        }catch (Exception e){
            e.printStackTrace();
            return false;
        }
    }

    public boolean addHouse(String[] value){
        String sql = "insert into house(houseType,totalPrice,averagePrice,allArea,insideArea,forward,community,Hfloor,totalFloor,years,types) " +
                "values(?,?,?,?,?,?,?,?,?,?,?)";
        try {
            ps = connection.prepareStatement(sql);
            ps.setString(1,value[0]);

            if(value[1].isEmpty()) ps.setDouble(2,0);
            else ps.setDouble(2,Double.parseDouble(value[1]));

            if(value[2].isEmpty()) ps.setDouble(3,0);
            else ps.setDouble(3,Double.parseDouble(value[2]));

            if(value[3].isEmpty()) ps.setDouble(4,0);
            else ps.setDouble(4,Double.parseDouble(value[3]));

            if(value[4].isEmpty()) ps.setDouble(5,0);
            else ps.setDouble(5,Double.parseDouble(value[4]));

            ps.setString(6,value[5]);
            ps.setString(7,value[6]);
            ps.setString(8,value[7]);
            ps.setString(9,value[8]);
            ps.setString(10,value[9]);
            ps.setString(11,value[10]);
            ps.executeUpdate();
            System.out.println("增加成功!");
            return true;
        }catch (Exception e){
            e.printStackTrace();
            return false;
        }
    }
    public boolean deleteCommunity(String id){
        String sql = "delete from community where id="+id;
        try {
            ps=connection.prepareStatement(sql);
            ps.executeUpdate();
            return true;
        }catch (Exception e){
            e.printStackTrace();
            return false;
        }
    }

    public boolean deleteHouse(String id){
        String sql = "delete from house where id="+id;
        try {
            ps=connection.prepareStatement(sql);
            ps.executeUpdate();
            return true;
        }catch (Exception e){
            e.printStackTrace();
            return false;
        }
    }
    public boolean alterCommunity(String attribute,String id,String newValue){
        String sql3 = "update community set "+attribute +"=? where  id =?";

        try {
            ps = connection.prepareStatement(sql3);
            ps.setString(2,id);
            if(attribute.equals("longitude")||attribute.equals("latitude")){
                ps.setDouble(1,Double.parseDouble(newValue));

            }
            else {
                ps.setString(1,newValue);
            }
            ps.executeUpdate();
            System.out.println("修改成功!!");
            return true;
        }catch (Exception e){
            e.printStackTrace();
            return false;
        }
    }

    public boolean alterHouse(String attribute,String id,String newValue){
        String sql3 = "update house set "+attribute +"=? where  id =?";

        try {
            ps = connection.prepareStatement(sql3);
            ps.setString(2,id);
            if(attribute.equals("totalPrice")||attribute.equals("averagePrice")||attribute.equals("allArea")
            ||attribute.equals("insideArea")){
                ps.setDouble(1,Double.parseDouble(newValue));
            }
            else {
                ps.setString(1,newValue);
            }
            ps.executeUpdate();
            System.out.println("修改成功!!");
            return true;
        }catch (Exception e){
            e.printStackTrace();
            return false;
        }
    }

    public  ResultSet findHousePoint(String id){
        String sql = "select* from house,community where house.community=community.name and house.id="+id;
        try {
            ps=connection.prepareStatement(sql);
            return  ps.executeQuery();
        }catch (Exception e){
            e.printStackTrace();
            return  null;
        }
    }

    public boolean addUser(String name,String password,String level){
        String sql ="insert into Users values(?,?,?)";
        try {
            ps =connection.prepareStatement(sql);
            ps.setString(1,name);
            ps.setString(2,password);
            ps.setString(3,level);
            ps.executeUpdate();
            return true;
        }catch (Exception e){
            return false;
        }
    }

    public boolean deleteUser(String name){
        String sql="delete from users where userName=?";
        try {
            ps =connection.prepareStatement(sql);
            ps.setString(1,name);
            ps.executeUpdate();
            return true;
        }catch (Exception e){
            return false;
        }
    }

    public ResultSet selectAllCommunity(){
        String sql = "select* from community";
        try {
           return ps.executeQuery(sql);
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }
    }
    public static void main(String arg[])
    {
        ConnectMySql connectMySql = new ConnectMySql("root","lishunlin");
        connectMySql.selectCommunity("星悦湾");
    }
}
